package com.ui.export.utils;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;

import cn.hutool.core.util.StrUtil;

public class ExpSql {

	public static List<Object> getTableInsertSql(Connection conn, String tableName, String where) throws Exception {
		ResultSet rs = null;
		Statement statement = null;
		List<Object> list = null;
		try {
			DatabaseMetaData metadata = conn.getMetaData();
			rs = metadata.getColumns(conn.getCatalog(), conn.getSchema(), tableName.toUpperCase(),null); // 得到表的字段列表

			String sql = "SELECT 'INSERT INTO " + tableName + "(";
			int count = 0;
			int counts = 0;
			// 获得列的总数
			while (rs.next()) {
				Object colName = rs.getObject("column_name");
				sql += colName+", ";
				count++;

			}
			sql = sql.substring(0, sql.length() - 2);
			sql += ") VALUES ( '";
			rs.close();
			// 重新获得列数据 整理成sql
			rs = metadata.getColumns(conn.getCatalog(), conn.getSchema(), tableName.toUpperCase(),null); // 得到表的字段列表
			while (rs.next()) {
				counts++;
				if (counts <= count) {
					Object colName = rs.getObject("column_name");
					sql += " ||'''' ||" + colName + "|| ''','";
				}
			}
			sql = sql.substring(0, sql.length() - 2) + "'";
			sql += " || ' );' FROM " + tableName;
			if (StrUtil.isNotEmpty(where)) {
				sql += " WHERE "+where ;
			}
			
			rs.close();

			// System.out.println("DEBUG: SQL="+sql);
			// 执行
			statement = conn.createStatement();
			rs = statement.executeQuery(sql);

			// 将SQL语句放到List中
			list = new ArrayList<Object>();
			while (rs.next())
				list.add(rs.getObject(1));
			rs.close();

			// System.out.println(list.size());
		} finally {
			if (rs != null)
				rs.close();
			if (statement != null)
				statement.close();
		}
		return list;
	}
	
	
	public static void main(String[] args) {
		try {
			DbUtils.loadDriver("oracle.jdbc.driver.OracleDriver");
			Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@10.16.5.69:1521/orcl.ufsoft.com.cn", "ncczr0401", "ncczr");
			System.out.println(getTableInsertSql(conn, "BD_REFINFO", " CODE IN  ('10H102P','10H102L','10H10221L','10H10225P','10H10201L','10H10223L','10H10227P','10H10221P','10H10201P','10H10223P','10H10226L','10H10222','10H10201','10H10223','10H10221','10H10226','10H10227','10H10224','10H10225','10H10222L','10H10226P','10H102','10H10224L','10H10222P','10H10224P','10H10225L','10H10227L');"));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}